﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class ChiTietPhieuXetNghiemDAO
    {
        public static DataTable LayDanhSachChiTietPhieuXetNghiem()
        {
            string sql;
            sql = "SELECT * FROM [ChiTietPhieuXetNghiem]";
            return DataAccess.ExecQuery(sql);
        }

        public static void ThemChiTietPhieuXetNghiem(ChiTietPhieuXetNghiemDTO obj)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO [ChiTietPhieuXetNghiem](TenXN, DonGia, KetQuaXetNghiem, MaPhieuXN) VALUES(N'{0}', {1}, N'{2}', {3})",
                                                obj.TenXN, obj.DonGia, obj.KetQuaXetNghiem, obj.MaPhieuXN);
            DataAccess.ExecNonQuery(sql);
        }

        public static void SuaChiTietPhieuXetNghiem(ChiTietPhieuXetNghiemDTO obj)
        {
            string sql = "";
            sql = string.Format(@"UPDATE [ChiTietPhieuXetNghiem]
                                SET TenXN = N'{0}', DonGia = {1}, KetQuaXetNghiem = N'{2}', MaPhieuXN = {3}
                                WHERE MaCTPXN = {4}", obj.TenXN, obj.DonGia, obj.KetQuaXetNghiem, obj.MaPhieuXN, obj.MaCTPXN);
            DataAccess.ExecNonQuery(sql);
        }

        public static void XoaChiTietPhieuXetNghiem(int maChiTietPhieuXetNghiem)
        {
            string sql;
            sql = string.Format("DELETE [ChiTietPhieuXetNghiem] WHERE MaCTPXN = {0}", maChiTietPhieuXetNghiem);
            DataAccess.ExecNonQuery(sql);
        }

        public static ChiTietPhieuXetNghiemDTO LayThongTinChiTietPhieuXetNghiemTheoMaChiTietPhieuXetNghiem(int maChiTietPhieuXetNghiem)
        {
            ChiTietPhieuXetNghiemDTO kq = new ChiTietPhieuXetNghiemDTO();
            string sql;
            sql = @"select * FROM [ChiTietPhieuXetNghiem]
                    where MaCTPXN = " + maChiTietPhieuXetNghiem;
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaCTPXN = Convert.ToInt32(dt.Rows[0]["MaCTPXN"]);
                kq.TenXN = Convert.ToString(dt.Rows[0]["TenXN"]);
                kq.DonGia = Convert.ToDouble(dt.Rows[0]["DonGia"]);
                kq.KetQuaXetNghiem = Convert.ToString(dt.Rows[0]["KetQuaXetNghiem"]);
                kq.MaPhieuXN = Convert.ToInt32(dt.Rows[0]["MaPhieuXN"]);                
            }
            return kq;
        }

        public static DataTable LayDanhSachChiTietPhieuXetNghiemTheoMaXN(int maPXN)
        {
            string sql;
            sql = "SELECT TenXN FROM [ChiTietPhieuXetNghiem] Where MaPhieuXN = "+ maPXN;
            return DataAccess.ExecQuery(sql);
        }

        public static DataTable LayDanhSachChiTietPhieuXetNghiemTheoMaPKB(string sql)
        {
            return DataAccess.ExecQuery(sql);
        }
    }
}
